##############################################################################
##
## Invoke-CancelSession.ps1
##
## Cancel a SSAS connection, session or spid. Optionally you can also cancel
## associated commands as well.
##
## e.g.:
##
##    ## Cancel a spid (from the local SSAS server. default instance)
##    Invoke-CancelSession.ps1 -spid <SPID>
##
##    ## Cancel a connection (from a remote server, default instance)
##    Invoke-CancelSession.ps1 -connection <ConnectionID> -server "WIN2K3R2EE"
##
##    ## Cancel a session (from a remote named instance)
##    Invoke-CancelSession.ps1 -session <SessionID> -server "WIN2K3R2EE\SALES"
##
## Additional info on the Cancel XMLA command:
##
##   <Cancel>
##      <ConnectionID>...</ConnectionID>
##      <SessionID>...</SessionID>
##      <SPID>...</SPID>
##      <CancelAssociated>...</CancelAssociated>
##   </Cancel>
##
## The ConnectionID element identifies an active connection to cancel.
## The SessionID element identifies an active session to cancel.
## The SPID element represents the server process ID (SPID) used for a given session on an instance
##      of Microsoft SQL Server 2005 Analysis Services (SSAS). It represents the unit of work for 
##      execution, i.e. the query, processing command, etc.
## If the CancelAssociated element is specified and set to True (the default is False), every
##      corresponding connection, session, and command identified in the parent Cancel command
##      is canceled.
##
##
## If you specify the ConnectionID, then the session and spid are ignored.
## If you specify the SessionID, then the spid is ignored. 
## You must specify either the ConnectionID, SessionID, or the SPID.
##
##############################################################################

param(
    [string] $connectionID = "",
    [string] $sessionID = "",
    [string] $spID = "",
    [string] $cancelAssociated = 0,
    [string] $server="localhost"
    )

## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

## Define the XMLA command to use
$xmlaCommand = "<Cancel xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">"
if ($connectionID.length -gt 0) {
        $xmlaCommand += "<ConnectionID>$connectionID</ConnectionID>"
    } elseif ($sessionID.length -gt 0) {
        $xmlaCommand += "<SessionID>$sessionID</SessionID>"
    } elseif ($spID.length -gt 0) {
        $xmlaCommand += "<SPID>$spID</SPID>"
    } else {throw "You must specify either the connection, session or spid to cancel."}
    
if ($cancelAssociated -gt 0) { $xmlaCommand += "<CancelAssociated>true</CancelAssociated>" }

$xmlaCommand += "</Cancel>"

write-Output "xmlaCommand: $xmlaCommand" ## debug

## Prepare the connection string based on information provided
$connectionString = "Data Source=$server"
write-Output "connectionString: $connectionString"

## Connect to the data source and open
##$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
##$command = New-Object System.Data.OleDb.OleDbCommand $xmlaCommand, $connection
##$connection.Open()

$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $connectionString
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText = $xmlaCommand
$result = $cmd.ExecuteNonQuery()

$conn.Close()

return $result

